package org.example.cet.demos.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface ScoreMapper {
    //    单次作业的完成人数
    @Select("select count(*) from score_bank where id in (select bank_id from submission where homework_id = #{homeworkId} )")
    Integer getOverPeopleSum(Integer homeworkId);

    //    每道题的正确率
    @Select("select count(*) / (select count(*) from submission where homework_id = #{shomeworkId} and question_id = #{squestionId}) as '正确率' from submission where JSON_EXTRACT(result, CONCAT('$[', #{xquestionId}-1, ']')) = '1' and question_id = #{questionId} and homework_id = #{homeworkId} ")
    Double getAccuracy(@Param("shomeworkId") Integer shomeworkId, @Param("squestionId") Integer squestionId, @Param("xquestionId") Integer xquestionId, @Param("questionId") Integer questionId, @Param("homeworkId") Integer homeworkId);

    //    每道题答对人数
    @Select("select COUNT(*) FROM submission where JSON_EXTRACT(result, CONCAT('$[', #{xquestionId}-1, ']')) = '1' and question_id = #{questionId} and homework_id = #{homeworkId}")
    Integer getRightSum(@Param("xquestionId") Integer xquestionId, @Param("questionId") Integer questionId, @Param("homeworkId") Integer homeworkId);

    //    每次模考班级专项平均分
    @Select("select avg(score)from score_detail d where d.type =#{qtype} and bank_id in " +
            "(select bank_id from submission s where homework_id=#{homeworkId} and s.type=#{type} and stu_id in" +
            "(select id from student where class_id = #{classId}))")
    Double getAverageScore(@Param("qtype") Integer qtype, @Param("homeworkId") Integer homeworkId, @Param("type") Integer type, @Param("classId") String classId);

    //    本次作业学生的总分
    @Select("select score from score_bank where id in (select bank_id from submission where homework_id =#{homeworkId} and stu_id=#{stuId})")
    Integer getScore(@Param("homeworkId") Integer homeworkId, @Param("stuId") String stuId);

    //    每次模考分段位人数
    @Select("select count(*)from score_bank where score >= #{score1} and score < #{score2}  and id in" +
            "(select bank_id from submission s where homework_id=#{homeworkId} and s.type=#{type} and stu_id in" +
            "(select id from student where class_id = #{classId}))")
    Integer getRankPeopleSum(@Param("score1") Integer score1, @Param("score2") Integer score2, @Param("homeworkId") Integer homeworkId, @Param("type") Integer type, @Param("classId") String classId);

}